package com.servicesDirectory.dao.organisation;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.servicesDirectory.common.exception.FunctionalException;
import com.servicesDirectory.common.exception.SystemException;
import com.servicesDirectory.common.vo.organisation.OrganisationVO;
import com.servicesDirectory.dao.government.BaseGovernmentDAOImpl;

public class OrganisationDAOImpl implements OrganisationDAO {
	public List<OrganisationVO> listOrganisation = null;
	public OrganisationVO organi = null;
	protected static Connection conn = null;
	protected static void initConnection()
            throws SQLException, ClassNotFoundException {

        if (conn == null) {
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//            String conUrl = "jdbc:sqlserver://localhost:0:1433;databaseName=MockPJ_ServiceDirectory;";
//            conn = DriverManager.getConnection(conUrl, "sa", "12345678");
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:MockPJ_ServiceDirectory", "sa", "12345678");
        }
    }
	@Override
	public boolean insert(OrganisationVO org) throws FunctionalException,
			SystemException {
		try {
            initConnection();

            Statement st = conn.createStatement();
            String strQuery = "INSERT INTO Organisation"
                    + "(OrganisationName,ShortDesc,ContactID,"
                    + "Addr1,Addr2,Addr3,Postcode,TownName,CountyName,CountryName,"
                    + "PreferredOrg,ExpOfInterest,TypeOfBusinessID,FullDesc,PhoneNumber,Fax,"
                    + "Email,WebAddr,CharityNumber,CompanyNumber,OrgSpecicalism,ServiceDisCap," 
                    + "ServiceBarCap,ServiceBenCap,ServicePerCirCap,ServiceEthCap,Accreditation," +
                    "EOIProgramme,EOIService) "
                    + "VALUES('"+org.getOrganisationName()+"','"+ org.getShortDesc()+"','"
                    +org.getContactID()+"','"+ org.getAddress1()+"','"
                    +org.getAddress2()+"','"+ org.getAddress3()+"','"
                    +org.getPostCode()+"','"+ org.getTownName()+"','"
                    +org.getCountyName()+"','"+org.getCountryName()+"','"
                    +org.getPreferredOrg()+"','"+ org.getExpOfInterest()+"','"
                    +org.getTypeOfBusinessID()+"','"+ org.getFullDesc()+"','"
                    +org.getPhoneNumber()+"','"+ org.getFax()+"','"
                    +org.getEmail()+"','"+ org.getWebAddress()+"','"
                    +org.getCharityNumber()+"','"+ org.getCompanyNumber()+"','"
                    +org.getOrgSpecicalism()+"','"+ org.getServiceDisCap()+"','"
                    +org.getServiceBarCap()+"','"+ org.getServiceBenCap()+"','"
                    +org.getServicePerCirCap()+"','"+ org.getServiceEthCap()+"','"
                    +org.getAccreditation()+"','"+ org.getEoiProgramme()+"','"
                    +org.getEoiService()+"')";
            


            int row = st.executeUpdate(strQuery);            

            if (row > 0) {
                return true;
            }
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }


        return false;
	}

	@Override
	public boolean update(OrganisationVO org) throws FunctionalException,
			SystemException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public List<OrganisationVO> list(String filter, boolean include)
			throws FunctionalException, SystemException {
		conn = null ;
		String qr = null;
		Statement st = null;
		organi = null;
		String qrNotInclude = " AND org.IsActive = 1";
		listOrganisation = new ArrayList<OrganisationVO>();

		try {
			initConnection();
			st = conn.createStatement();
			if (include == true) {
				if (filter.equals("ABCDE")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " OrganisationName LIKE 'A%' "
							+ "OR OrganisationName LIKE 'B%' "
							+ "OR OrganisationName LIKE 'C%'"
							+ "OR OrganisationName LIKE 'D%' "
							+ "OR OrganisationName LIKE 'E%'";
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " OrganisationName LIKE 'F%' "
							+ "OR OrganisationName LIKE 'G%' "
							+ "OR OrganisationName LIKE 'H%'"
							+ "OR OrganisationName LIKE 'I%' "
							+ "OR OrganisationName LIKE 'J%'";
				} else if (filter.equals("KLMN")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " OrganisationName LIKE 'K%' "
							+ "OR OrganisationName LIKE 'L%' "
							+ "OR OrganisationName LIKE 'M%'"
							+ "OR OrganisationName LIKE 'N%'";
				} else if (filter.equals("OPQR")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " OrganisationName LIKE 'O%' "
							+ "OR OrganisationName LIKE 'P%' "
							+ "OR OrganisationName LIKE 'Q%' "
							+ "OR OrganisationName LIKE 'R%'";
				} else if (filter.equals("STUV")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " OrganisationName LIKE 'S%' "
							+ "OR OrganisationName LIKE 'T%' "
							+ "OR OrganisationName LIKE 'U%' "
							+ "OR OrganisationName LIKE 'V%'";
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " OrganisationName LIKE 'W%' "
							+ "OR OrganisationName LIKE 'X%' "
							+ "OR OrganisationName LIKE 'Y%' "
							+ "OR OrganisationName LIKE 'Z%'";
				} else if (filter.equals("0-9")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " OrganisationName LIKE '0%' "
							+ "OR OrganisationName LIKE '1%' "
							+ "OR OrganisationName LIKE '2%' "
							+ "OR OrganisationName LIKE '3%'"
							+ "OR OrganisationName LIKE '4%' "
							+ "OR OrganisationName LIKE '5%' "
							+ "OR OrganisationName LIKE '6%' "
							+ "OR OrganisationName LIKE '7%' "
							+ "OR OrganisationName LIKE '8%' "
							+ "OR OrganisationName LIKE '9%' ";

				} else {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID ";
				}
			} else {
				if (filter.equals("ABCDE")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " (OrganisationName LIKE 'A%' "
							+ "OR OrganisationName LIKE 'B%' "
							+ "OR OrganisationName LIKE 'C%'"
							+ "OR OrganisationName LIKE 'D%' "
							+ "OR OrganisationName LIKE 'E%') " + qrNotInclude;
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " (OrganisationName LIKE 'F%' "
							+ "OR OrganisationName LIKE 'G%' "
							+ "OR OrganisationName LIKE 'H%'"
							+ "OR OrganisationName LIKE 'I%' "
							+ "OR OrganisationName LIKE 'J%') " + qrNotInclude;
				} else if (filter.equals("KLMN")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " (OrganisationName LIKE 'K%' "
							+ "OR OrganisationName LIKE 'L%' "
							+ "OR OrganisationName LIKE 'M%'"
							+ "OR OrganisationName LIKE 'N%') " + qrNotInclude;
				} else if (filter.equals("OPQR")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , Contact.firstName, Contact.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " (OrganisationName LIKE 'O%' "
							+ "OR OrganisationName LIKE 'P%' "
							+ "OR OrganisationName LIKE 'Q%' "
							+ "OR OrganisationName LIKE 'R%) '" + qrNotInclude;
				} else if (filter.equals("STUV")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " (OrganisationName LIKE 'S%' "
							+ "OR OrganisationName LIKE 'T%' "
							+ "OR OrganisationName LIKE 'U%' "
							+ "OR OrganisationName LIKE 'V%') " + qrNotInclude;
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " (OrganisationName LIKE 'W%' "
							+ "OR OrganisationName LIKE 'X%' "
							+ "OR OrganisationName LIKE 'Y%' "
							+ "OR OrganisationName LIKE 'Z%') " + qrNotInclude;
				} else if (filter.equals("0-9")) {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE "
							+ " (OrganisationName LIKE '0%' "
							+ "OR OrganisationName LIKE '1%' "
							+ "OR OrganisationName LIKE '2%' "
							+ "OR OrganisationName LIKE '3%'"
							+ "OR OrganisationName LIKE '4%' "
							+ "OR OrganisationName LIKE '5%' "
							+ "OR OrganisationName LIKE '6%' "
							+ "OR OrganisationName LIKE '7%' "
							+ "OR OrganisationName LIKE '8%' "
							+ "OR OrganisationName LIKE '9%')  " + qrNotInclude;

				} else {
					qr = "SELECT org.OrganisationID, org.OrganisationName, org.Addr1, org.Postcode, org.IsActive , con.firstName, con.surName "
							+ "FROM Organisation org INNER JOIN Contact con ON org.ContactID = con.ContactID WHERE org.IsActive = 1 ";
				}
			}
				ResultSet rs = st.executeQuery(qr);
				while(rs.next()){
					int orgnisationID = rs.getInt(1);
					String orgName = rs.getString(2);
					String address = rs.getString(3);
					String code = rs.getString(4);
					int isActive = rs.getInt(5);
					String name = rs.getString(6)+ " " + rs.getString(7);				
					String stringActive;
					if(isActive == 1){
						stringActive = "Yes";
					}
					else{
						stringActive = "No";
					}
					organi = new OrganisationVO(orgnisationID, orgName, address, code, stringActive, name);
					listOrganisation.add(organi);
					
				}
			
			

			
		} catch (SQLException ex) {
            Logger.getLogger(OrganisationDAOImpl.class.getName()).log(
                    Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(OrganisationDAOImpl.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            /*
             * 
             * Close all streams
             */
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(OrganisationDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(OrganisationDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
		return listOrganisation;
	}

}
